import asyncmy
from core.config import settings

class MySQL:
    def __init__(self):
        self.pool = None

    async def connect(self):
        self.pool = await asyncmy.create_pool(
            host=settings.MYSQL_HOST,
            port=settings.MYSQL_PORT,
            user=settings.MYSQL_USER,
            password=settings.MYSQL_PASSWORD,
            db=settings.MYSQL_DB,
            autocommit=True,
            pool_recycle=300
        )
    async def close(self):
        if self.pool:
            self.pool.close()
            await self.pool.wait_closed()
            
    async def ensure_connection(self):
        if self.pool is None:
            await self.connect()

    async def check_connection(self):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cur:
                    await cur.execute("SELECT 1")
                    return True
        except Exception as e:
            print(f"MySQL connection check failed: {str(e)}") 
            return False

    async def fetch_one(self, query, params=None):
        await self.ensure_connection()
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(query, params)
                result = await cur.fetchone()
                if result:
                    return dict(zip([col[0] for col in cur.description], result))
                return None

    async def fetch_all_ori(self, query, params=None):
        await self.ensure_connection()
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(query, params)
                result = await cur.fetchall()
                return result

    async def fetch_all(self, query, params=None):
        await self.ensure_connection()
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute(query, params)
                results = await cur.fetchall()
                if results:
                    columns = [col[0] for col in cur.description]
                    return [dict(zip(columns, row)) for row in results]
                return []

    async def execute(self, query, params=None):
        await self.ensure_connection()
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cur:
                result = await cur.execute(query, params)
                return result

# 创建全局MySQL实例
mysql = MySQL()